﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Jenzabar.Portal.Framework.Web.UI;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using Jenzabar.Portal.Framework;

namespace Portlet.RHFSContract
{
    public partial class Admin_View : PortletViewBase
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            gv.Visible = true;
            gvSearch.Visible = false;
            if (IsFirstLoad)
            {
                BindGV(gv);
                PopulateSession();
                ddSession.Items.Add(new ListItem("", ""));
                ddSession.SelectedValue = "";
            }
        }

        private void PopulateSession()
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SELECT [SESS_CDE] ,[SESS_DESC]  FROM [TmsEprd].[dbo].[CM_SESSION_MSTR]  Where [SESS_SESSION_SEQ]  > 23 and  [SESS_SESSION_SEQ]  <  33  ORDER BY SESS_SESSION_SEQ DESC", myConn);
                //cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader rdr;

                try
                {
                    myConn.Open();
                    rdr = cmd.ExecuteReader();
                    ddSession.DataSource = rdr;
                    ddSession.DataValueField = "SESS_CDE";
                    ddSession.DataTextField = "SESS_DESC";
                    ddSession.DataBind();
                    rdr.Close();

                }
                catch (Exception ex)
                {
                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error retrieving Data. " + ex.Message);


                }
                finally
                {

                    myConn.Close();
                }
            }
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            string  session;
            gv.Visible = false;
            session = ddSession.SelectedValue.ToString().Trim();
            lblSession.Text = session;
            if (session != "")
            {
                gvSearch.Visible = true;
                Session["session"] = session;

                Bind(gvSearch, session);


            }
            //BindGV(gv);
        }

        private void BindGV(GridView gv1)
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {

                try
                {

                    myConn.Open();
                    SqlCommand cmd = new SqlCommand("Select  * FROM [TmsEprd].[dbo].[CUS_Housing_Contract]", myConn);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    //DataTable dt = new DataTable();

                    DataSet ds = new DataSet();

                    da.Fill(ds, "DataView");
                    Session["myTable"] = ds;
                    gv1.DataSource = ds;
                    gv1.DataBind();

                }
                catch (Exception ex)
                {


                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Binding Data Items: " + ex.Message);
                }
                finally
                {
                    // Close Connection
                    myConn.Close();
                }
            }
        }

        protected void gv_SelectedIndexChanged(object sender, EventArgs e)
        {
          
           
        }

        private void populateDV()
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {

                try
                {
                    myConn.Open();
                    //GridViewRow selectedRow = gvPlayer.SelectedRow;
                    int selectedIndex = gvSearch.SelectedIndex;
                    //Read the ID From the row
                    string id = (gvSearch.DataKeys[selectedIndex].Value).ToString();

                    // selectedRowSID = selectedRow.Cells[0].Text;
                    SqlCommand cmd = new SqlCommand("SELECT * FROM [TmsEprd].[dbo].[CUS_Housing_Contract] WHERE ID_NUM= '" + id + "'", myConn);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    DataSet ds = new DataSet();

                    da.Fill(ds, "DataView");

                    //SqlDataReader reader;
                    //reader = cmd.ExecuteReader();
                    dv.DataSource = ds;
                    dv.DataBind();
                }
                catch (Exception ex)
                {
                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error retrieving Data. test " + ex.Message);
                    // this.ParentPortlet.NextScreen("Default");

                }
                finally
                {

                    myConn.Close();
                }

            }
        }


        public class lstDropDown
        {
            public string _sCode;
            public string _sValue;

            public lstDropDown(string sCode, string sValue)
            {
                this._sCode = sCode;
                this._sValue = sValue;

            }
            public string StatusCode
            {
                get { return _sCode; }
                set { _sCode = value; }

            }

            public string StatusValue
            {
                get { return _sValue; }
                set { _sValue = value; }

            }

        }

        protected void dv_DataBound(object sender, EventArgs e)
        {

            if (dv.CurrentMode == DetailsViewMode.Edit)
            {
                DropDownList dd = (DropDownList)dv.FindControl("ddMeal");
                DropDownList ddR = (DropDownList)dv.FindControl("ddRoomType");
                TextBox meal = (TextBox)dv.FindControl("txtMeal");
                TextBox roomtype = (TextBox)dv.FindControl("txtRoomType");
               // Label deposit = (Label)dv.FindControl("(lblDeposit");

                if (dd != null)
                {
                    List<lstDropDown> lstStr = new List<lstDropDown>();
                    lstStr.Add(new lstDropDown("", ""));
                    lstStr.Add(new lstDropDown("14-Meal Plan", "R1"));
                    lstStr.Add(new lstDropDown("19-Carte Balance Plan", "R2"));
                    lstStr.Add(new lstDropDown("Thompson 7-Meal Plan", "R3"));


                    dd.DataSource = lstStr;
                    dd.DataValueField = "StatusValue";
                    dd.DataTextField = "StatusCode";  
                    dd.DataBind();

                    
                   
                }

                if (ddR != null)
                {
                    List<lstDropDown> lstStr = new List<lstDropDown>();
                    lstStr.Add(new lstDropDown("", ""));

                    lstStr.Add(new lstDropDown("Single Room - Carondelet Hall", "SC"));
                    lstStr.Add(new lstDropDown("Single Room - Ridgway Hall", "SG"));
                    lstStr.Add(new lstDropDown("Single Room - Avila Hall", "SR"));
                    lstStr.Add(new lstDropDown("Double Room - Carondelet Hall", "DC"));
                    lstStr.Add(new lstDropDown("Double Room - Ridgway Hall", "DB"));
                    lstStr.Add(new lstDropDown("Triple Room - Carondelet Hall", "TR"));
                    lstStr.Add(new lstDropDown("Triple Room - Ridgway Hall", "TR"));

                    ddR.DataSource = lstStr;
                    ddR.DataValueField = "StatusValue";
                    ddR.DataTextField = "StatusCode";
                    ddR.DataBind();
                }

                //dropdown populated already existed data for this add the text control in DV(dv) make it visible false
                dd.SelectedValue = meal.Text;
                ddR.SelectedValue = roomtype.Text;

            }
        }

        protected void dv_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
        {
            int selectedIndex = gvSearch.SelectedIndex;
            //Read the ID From the row
            string SID = (gvSearch.DataKeys[selectedIndex].Value).ToString();
            DropDownList ddM = (DropDownList)dv.FindControl("ddMeal");
            DropDownList ddR = (DropDownList)dv.FindControl("ddRoomType");
            TextBox rid = (TextBox)dv.FindControl("txtRID");
         
            UpdateContract(SID, ddM.SelectedValue.ToString(), ddR.SelectedValue.ToString(), ddSession.SelectedValue.ToString().Trim());
            
            UpdateEx(SID, ddM.SelectedValue.ToString(), ddR.SelectedValue.ToString(), ddSession.SelectedValue.ToString().Trim());
            if (rid.Text != "")
            {
                UpdateRoomate(SID, ddSession.SelectedValue.ToString().Trim(), rid.Text); // Coming soon update roommate ID in [TmsEPly].[dbo].[STUD_ROOMMATES]
            }
            dv.Visible = false;
            Bind(gvSearch, ddSession.SelectedValue.ToString().Trim() );
        }

        private void UpdateRoomate(string SID, string sess, string r)
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {

                try
                {
                    myConn.Open();

                    SqlCommand cmd = new SqlCommand("CUS_sp_HousingContract_STUD_Roommate_Insert", myConn);
                    cmd.Parameters.Add("@sid", SqlDbType.VarChar, 10).Value = SID;
                   
                    cmd.Parameters.Add("@roomate", SqlDbType.VarChar, 10).Value = r;
                    cmd.Parameters.Add("@session", SqlDbType.VarChar, 10).Value = sess;

                    string user = PortalUser.Current.Username.ToString().ToUpper().Trim();
                    cmd.Parameters.Add("@user", SqlDbType.VarChar, 10).Value = user;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {


                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Inserting Data Items in STUD_ROOMMATE: The Roomate ID " + r + " Not available for this Session: "+ sess + "<br />" + ex.Message);
                }
                finally
                {
                    // Close Connection
                    myConn.Close();
                }
            }
            
        }
        //UPDATE [TmsEprd].[dbo].STUD_SESS_ASSIGN
        private void UpdateEx(string SID, string meal, string roomtype, string session)
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {

                try
                {
                    myConn.Open();

                    SqlCommand cmd = new SqlCommand("CUS_sp_HousingContract_Sess_assign", myConn);
                    cmd.Parameters.Add("@sid", SqlDbType.VarChar, 10).Value = SID;
                    cmd.Parameters.Add("@mealplan", SqlDbType.VarChar, 10).Value = meal;
                    cmd.Parameters.Add("@roomtype", SqlDbType.VarChar, 10).Value = roomtype;
                    cmd.Parameters.Add("@session", SqlDbType.VarChar, 10).Value = session;
                   // cmd.Parameters.Add("@deposit", SqlDbType.DateTime, 15).Value = dep;
                    string user = PortalUser.Current.Username.ToString().ToUpper().Trim();
                    
                    cmd.Parameters.Add("@user", SqlDbType.VarChar, 10).Value = user;
                   
                    
                    cmd.CommandType = CommandType.StoredProcedure;
                  

                    cmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {


                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Inserting Data Items in STUD_SESS_ASSIGN: " + ex.Message);
                }
                finally
                {
                    // Close Connection
                    myConn.Close();
                }
            }
        }

        private void UpdateContract(string SID, string meal, string roomtype, string session)
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {

                try
                {
                    myConn.Open();

                    SqlCommand cmd = new SqlCommand("CUS_sp_HousingContract_Update", myConn);
                    cmd.Parameters.Add("@sid", SqlDbType.VarChar, 10).Value = SID;
                    cmd.Parameters.Add("@mealplan", SqlDbType.VarChar, 10).Value = meal;
                    cmd.Parameters.Add("@roomtype", SqlDbType.VarChar, 10).Value = roomtype;
                    cmd.Parameters.Add("@session", SqlDbType.VarChar, 10).Value = session; //--SESS_CDE added on 4/10/2015 for Current Session code for my references
                    
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {


                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Updating Data Items in:CUS_HousingContract " + ex.Message);
                }
                finally
                {
                    // Close Connection
                    myConn.Close();
                }
            }
        }

        protected void dv_ModeChanging(object sender, DetailsViewModeEventArgs e)
        {
            dv.ChangeMode(e.NewMode);
            populateDV();
            if (e.NewMode == DetailsViewMode.ReadOnly)
            {
                if (e.CancelingEdit || e.Cancel)
                {
                    dv.Visible = false;

                }
            }
        }

        protected void ddSession_SelectedIndexChanged(object sender, EventArgs e)
        {
            gv.Visible = false;
            dv.Visible = false;
            string session = ddSession.SelectedValue.ToString().Trim();
            if (session != "")
            {
                gvSearch.Visible = true;
                Session["session"] = session;
              
                Bind(gvSearch, session);
                

            }
        }

        private void Bind(GridView gv, string session)
        {
             using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {

                try
                {

                    string term, year;
                    int len, pos;
                    session = ddSession.SelectedValue.ToString().Trim(); // 
                    len = session.Length; // if session =  "FA 2012"  so length = 7
                    term = session.Substring(0, 2); // if session =  FA 2012  then term = FA
                    pos = len - 4;  // 7- 4 = 3
                    year = session.Substring(pos, 4); // Position 3 ((ie position 0123456)actually start from 0) and length 4 ie 2012
                
                    myConn.Open();
                    //BIRTH_DTE = CONVERT( varchar(15), chc.BIRTH_DTE,101),chc.MEAL_PLAN, 
                    string sql = "Select DISTINCT chc.ID_NUM, Name = chc.Name, chc.Age, chc.GENDER, chc.CLEAN, chc.SMOKE, ACTIVITY = CONVERT(VARCHAR(MAX),chc.ACTIVITY),  DESCRIBE = CONVERT(VARCHAR(MAX), chc.DESCRIBE),chc.Major, chc.ROOMMATE,chc.ROOMMATE_NAME, chc.ROOM_TYPE  FROM [TmsEprd].[dbo].[CUS_Housing_Contract] chc ";
                    sql += " inner join TmsEPrd.dbo.[STUDENT_DIV_MAST] sdm on sdm.ID_NUM = chc.ID_NUM ";

                    sql += " inner join TmsEPrd.dbo.STUDENT_MASTER sm on sm.ID_NUM = sdm.ID_NUM and sm.CUR_STUD_DIV = sdm.DIV_CDE ";
                    sql += " inner join TmsEPrd.dbo.[DEGREE_HISTORY] dh on sdm.ID_NUM = dh.ID_NUM ";
                    sql += " inner join TmsEPrd.dbo.STUDENT_CRS_HIST sch on sdm.ID_NUM = sch.ID_NUM ";
                    sql += " WHERE dh.CUR_DEGREE ='Y'  and dh.ACTIVE ='Y' and sch.TRM_CDE  ='" + term + "' and sch.YR_CDE = " + year  ;
                  

                 
                    
                    
                    SqlCommand cmd = new SqlCommand(sql, myConn);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    //DataTable dt = new DataTable();

                    DataSet ds = new DataSet();

                    da.Fill(ds, "DataView");
                    Session["myTable"] = ds;
                    gv.DataSource = ds;
                    gv.DataBind();
                }
                catch (Exception ex)
                {


                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Binding Data Items: " + ex.Message);
                }
                finally
                {
                    // Close Connection
                    myConn.Close();
                }
            }
        }

        protected void gv_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            // For Paging 
            gv.Visible = true;
            gv.PageIndex = e.NewPageIndex;
            BindGV(gv);
        }

        protected void gvSearch_SelectedIndexChanged(object sender, EventArgs e)
        {
            GridViewRow row = gvSearch.SelectedRow;
            dv.Visible = true;
            //dv to edit Mode
            dv.ChangeMode(DetailsViewMode.Edit);
            populateDV();
            gv.Visible = false;
            gvSearch.Visible = true;


            string session = ddSession.SelectedValue.ToString().Trim();
            if (session != "")
            {
                gvSearch.Visible = true;
                Session["session"] = session;

                Bind(gvSearch, session);


            }
        }

        protected void gvSearch_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            string ses;
            // For Paging 
           
            gvSearch.PageIndex = e.NewPageIndex;
            gv.Visible = false;
            dv.Visible = false;

            ses = ddSession.SelectedValue.ToString().Trim();
            if (ses != "")
            {
                gvSearch.Visible = true;
                Bind(gvSearch, ses);


            }
        }


    }
}